﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using WimMain.Common;
using NPOI.XSSF.UserModel;
using System.Reflection;
using Action = System.Action;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Reporting.WebForms;
using System.Data;
using WimMain.Fun.Askform;
using WimMain.Models;

namespace WimMain.Fun
{
    /// <summary>
    /// 丰田
    /// </summary>
    public class FengTian : BaseAsk
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="contentStr"></param>
        public FengTian(string contentStr) : base(contentStr) { }


        /// <summary>
        /// 导出所有报告
        /// </summary>
        /// <param name="path">导出路径</param>
        /// <returns></returns>
        public Result ExportExcel(string path)
        {
            return RunFun(() =>
            {
                long formID = 8463430001;
                long formIDByJp = 8463450001;

                int oldYear = 2018;

                path = ToolFile.GetAbsolutelyPath(path);
                string modelPath = ToolFile.GetAbsolutelyPath(ModelPath);

                int maxCount = 10;// 导出最大数量，0为所有

                Dictionary<string, Action<string, string>> dic = new Dictionary<string, Action<string, string>>
                {
                    { "丰田-满意度-部-Excel.rdl", (mp, p) => BuReport(mp, p,"zh-cn", formID, oldYear,LogPath,maxCount) },
                    { "丰田-满意度-室-Excel.rdl",  (mp, p) => ShiReport(mp, p,"zh-cn", formID, oldYear,LogPath,maxCount) },
                    { "丰田-满意度-课-Excel.rdl", (mp, p) => KeReport(mp, p,"zh-cn", formID, oldYear,LogPath,maxCount) },

                    { "丰田-满意度-部-Excel-日文.rdl", (mp, p) => BuReport(mp, p,"ja-jp", formIDByJp, oldYear,LogPath,maxCount) },
                    { "丰田-满意度-室-Excel-日文.rdl",  (mp, p) => ShiReport(mp, p,"ja-jp", formIDByJp, oldYear,LogPath,maxCount) },
                    { "丰田-满意度-课-Excel-日文.rdl", (mp, p) => KeReport(mp, p,"ja-jp", formIDByJp, oldYear,LogPath,maxCount)  }
                };

                foreach (var k in dic.Keys)
                {
                    WriteLog(LogPath, "报告模板：" + k);
                    dic[k].Invoke(modelPath + k, path);
                }
                return Res;
            });
        }

        /// <summary>
        /// 导出PDF
        /// </summary>
        /// <param name="path">Excel文件夹</param>
        /// <param name="targetPath">导出地址</param>
        /// <param name="isToPdf">是否转PDF</param>
        /// <returns></returns>
        public Result ExportPDF(string path, string targetPath, bool isToPdf)
        {
            return RunFun(() =>
            {
                path = ToolFile.GetAbsolutelyPath(path, false);
                targetPath = ToolFile.GetAbsolutelyPath(targetPath, false);

                if (!Directory.Exists(path))
                {
                    WriteLog(LogPath, "未找到路径：" + path);
                    return Res;
                }

                ExportPDFByDirectory(path, targetPath, isToPdf);

                return Res;
            });
        }

        #region 导出Excel

        /// <summary>
        /// 导出部报告
        /// </summary>
        /// <param name="modelPath">报告模板地址</param>
        /// <param name="path">导出地址</param>
        /// <param name="language">语言</param>
        /// <param name="formID">表单ID</param>
        /// <param name="oldYear">对比年份</param>
        /// <param name="LogPath">日志路径</param>
        /// <param name="maxCon">最大导出数量，0不受限制</param>
        public void BuReport(string modelPath, string path, string language, long formID, int oldYear, string LogPath, int maxCon = 0)
        {
            path = ToolFile.GetAbsolutelyPath(path);
            BaseAsk bf = GetParam(formID);

            ReportParameterCollection col = ToolReport.BindPara(modelPath, formID, false);

            col.Add(new ReportParameter("OldYear", oldYear + ""));
            col.Add(new ReportParameter("MinValue", "0"));

            col.Add(new ReportParameter("BuChoiceID", ""));

            string sql = string.Format(@"
                declare @CompanyID int = {0}, @FormApplicationID bigint = {1}, @FormID bigint = {2}

                select distinct c.ChoiceID as BuChoiceID, p.Bu, p.Bu_jp
                from AskForm_Field f
                join AskForm_Choice c on c.FieldID = f.FieldID
                join Temp_TMCIPosition p on p.Bu = c.Text
                where f.CompanyID = @CompanyID and f.FormApplicationID = @FormApplicationID and f.FormID = @FormID
                and f.Name = 'Bu' and p.PositionType = '部'
            ", bf.CompanyID, bf.FormApplicationID, bf.FormID);
            DataTable dt = DbContent.GetTable(sql);
            Dictionary<string, int> dic = GetCount(formID);

            WriteLog(LogPath, "共" + dt.Rows.Count + "，" + dic.Keys.Count);

            if (maxCon == 0)
                maxCon = int.MaxValue;
            int importCon = 0;
            foreach (DataRow dr in dt.Rows)
            {
                if (importCon >= maxCon)
                {
                    WriteLog(LogPath, "已导出最大数量：" + maxCon);
                    break;
                }
                string buChoiceID = dr["BuChoiceID"].ToString();
                col["BuChoiceID"].Values[0] = buChoiceID;

                string bu;
                if (language == "zh-cn")
                {
                    bu = dr["Bu"].ToString();
                }
                else
                {
                    bu = dr["Bu_jp"].ToString();
                }

                // 校验，人数少于三个的不进行导出
                if (dic.Keys.Contains("Bu_" + buChoiceID))
                {
                    int con = dic["Bu_" + buChoiceID];
                    if (con < 3)
                    {
                        WriteLog(LogPath, "X\t" + bu + "，人数为" + con);
                        continue;
                    }
                }
                else
                {
                    WriteLog(LogPath, "X\t" + bu + "，不存在");
                    continue;
                }
                importCon++;
                WriteLog(LogPath, bu + ".xls");
                if (string.IsNullOrWhiteSpace(bu)) bu = "随机_bu_" + ToolString.GetRandomStr(10, 6);
                string reportTargetPath = string.Format(@"{0}FengTian\{1}\{2}\", path, language, bu);
                ToolReport.GenerateLocalReport(modelPath, reportTargetPath, bu + ".xls", col, false);
            }
        }

        /// <summary>
        /// 导出室报告
        /// </summary>
        /// <param name="modelPath">报告模板地址</param>
        /// <param name="path">导出地址</param>
        /// <param name="language">语言</param>
        /// <param name="formID">表单ID</param>
        /// <param name="oldYear">对比年份</param>
        /// <param name="LogPath">日志路径</param>
        /// <param name="maxCon">最大导出数量，0不受限制</param>
        private void ShiReport(string modelPath, string path, string language, long formID, int oldYear, string LogPath, int maxCon = 0)
        {
            path = ToolFile.GetAbsolutelyPath(path);
            BaseAsk bf = GetParam(formID);


            ReportParameterCollection col = ToolReport.BindPara(modelPath, formID, false);
            col.Add(new ReportParameter("OldYear", oldYear + ""));
            col.Add(new ReportParameter("MinValue", "0"));

            col.Add(new ReportParameter("BuChoiceID", ""));
            col.Add(new ReportParameter("ShiChoiceID", ""));

            string sql = string.Format(@"
                declare @CompanyID int = {0}, @FormApplicationID bigint = {1}, @FormID bigint = {2}

				select c.ChoiceID, f.Name, c.Text
				into #Choices
                from AskForm_Field f
                join AskForm_Choice c on c.FieldID = f.FieldID
                where f.CompanyID = @CompanyID and f.FormApplicationID = @FormApplicationID and f.FormID = @FormID
                and f.Name in ('Bu', 'Shi')
                
                select t.*,
				(
					select MAX(ChoiceID) from #Choices where Name = 'Bu' and Text = t.Bu
				) as BuChoiceID,
				(
					select MAX(ChoiceID) from #Choices where Name = 'Shi' and  Text = t.Shi
				) as ShiChoiceID
                from (
					select distinct Bu, Bu_jp, Shi, Shi_jp
					from Temp_TMCIPosition p
					where Shi is not null AND p.Year='{3}'
                ) t
				
				drop table #Choices
            ", bf.CompanyID, bf.FormApplicationID, bf.FormID, oldYear);
            DataTable dt = DbContent.GetTable(sql);
            Dictionary<string, int> dic = GetCount(formID);
            WriteLog(LogPath, "共" + dt.Rows.Count + "，" + dic.Keys.Count);

            if (maxCon == 0)
                maxCon = int.MaxValue;
            int importCon = 0;
            foreach (DataRow dr in dt.Rows)
            {
                if (importCon >= maxCon)
                {
                    WriteLog(LogPath, "已导出最大数量：" + maxCon);
                    break;
                }
                string buChoiceID = dr["BuChoiceID"].ToString();
                string shiChoiceID = dr["ShiChoiceID"].ToString();
                col["BuChoiceID"].Values[0] = buChoiceID;
                col["ShiChoiceID"].Values[0] = shiChoiceID;
                string bu, shi;
                if (language == "zh-cn")
                {
                    bu = dr["Bu"].ToString();
                    shi = dr["Shi"].ToString();
                }
                else
                {
                    bu = dr["Bu_jp"].ToString();
                    shi = dr["Shi_jp"].ToString();
                }

                // 校验，人数少于三个的不进行导出
                if (dic.Keys.Contains("Shi_" + shiChoiceID))
                {
                    int con = dic["Shi_" + shiChoiceID];
                    if (con < 3)
                    {
                        WriteLog(LogPath, "X\t" + bu + "\\" + shi + "，人数为" + con);
                        continue;
                    }
                }
                else
                {
                    WriteLog(LogPath, "X\t" + bu + "\\" + shi + "，不存在");
                    continue;
                }

                WriteLog(LogPath, bu + "\\" + shi + ".xls");
                importCon++;
                string reportTargetPath = string.Format(@"{0}FengTian\{1}\{2}\", path, language, bu);
                if (string.IsNullOrWhiteSpace(shi)) shi = "随机_shi_" + ToolString.GetRandomStr(10, 6);
                ToolReport.GenerateLocalReport(modelPath, reportTargetPath, shi + ".xls", col, false);
            }
        }

        /// <summary>
        /// 导出课报告
        /// </summary>
        /// <param name="modelPath">报告模板地址</param>
        /// <param name="path">导出地址</param>
        /// <param name="language">语言</param>
        /// <param name="formID">表单ID</param>
        /// <param name="oldYear">对比年份</param>
        /// <param name="LogPath">日志路径</param>
        /// <param name="maxCon">最大导出数量，0不受限制</param>
        private void KeReport(string modelPath, string path, string language, long formID, int oldYear, string LogPath, int maxCon = 0)
        {
            path = ToolFile.GetAbsolutelyPath(path);
            BaseAsk bf = GetParam(formID);


            ReportParameterCollection col = ToolReport.BindPara(modelPath, formID, false);
            col.Add(new ReportParameter("OldYear", oldYear + ""));
            col.Add(new ReportParameter("MinValue", "0"));

            col.Add(new ReportParameter("BuChoiceID", ""));
            col.Add(new ReportParameter("ShiChoiceID", ""));
            col.Add(new ReportParameter("KeChoiceID", ""));

            string sql = string.Format(@"
                declare @CompanyID int = {0}, @FormApplicationID bigint = {1}, @FormID bigint = {2}

				select c.ChoiceID, f.Name, c.Text
				into #Choices
                from AskForm_Field f
                join AskForm_Choice c on c.FieldID = f.FieldID
                where f.CompanyID = @CompanyID and f.FormApplicationID = @FormApplicationID and f.FormID = @FormID
                and f.Name in ('Bu', 'Ke')
                
                select t.*,
				(
					select MAX(ChoiceID) from #Choices where Name = 'Bu' and Text = t.Bu
				) as BuChoiceID,
				(
					select MAX(ChoiceID) from #Choices where Name = 'Ke' and  Text = t.Ke
				) as KeChoiceID
                from (
					select distinct Bu, Bu_jp, Shi, Shi_jp, Ke, Ke_jp
					from Temp_TMCIPosition p
					where Ke is not null AND p.Year='{3}'
                ) t
				
				drop table #Choices
            ", bf.CompanyID, bf.FormApplicationID, bf.FormID, oldYear);
            DataTable dt = DbContent.GetTable(sql);
            Dictionary<string, int> dic = GetCount(formID);
            WriteLog(LogPath, "共" + dt.Rows.Count + "，" + dic.Keys.Count);
            if (maxCon == 0)
                maxCon = int.MaxValue;
            int importCon = 0;
            foreach (DataRow dr in dt.Rows)
            {
                if (importCon >= maxCon)
                {
                    WriteLog(LogPath, "已导出最大数量：" + maxCon);
                    break;
                }
                string buChoiceID = dr["BuChoiceID"].ToString();
                string keChoiceID = dr["KeChoiceID"].ToString();
                col["BuChoiceID"].Values[0] = buChoiceID;
                col["KeChoiceID"].Values[0] = keChoiceID;
                string bu, shi, ke;
                if (language == "zh-cn")
                {
                    bu = dr["Bu"].ToString();
                    shi = dr["Shi"].ToString();
                    ke = dr["Ke"].ToString();
                }
                else
                {
                    bu = dr["Bu_jp"].ToString();
                    shi = dr["Shi_jp"].ToString();
                    ke = dr["Ke_jp"].ToString();
                }

                // 校验，人数少于三个的不进行导出
                if (dic.Keys.Contains("Ke_" + keChoiceID))
                {
                    int con = dic["Ke_" + keChoiceID];
                    if (con < 3)
                    {
                        WriteLog(LogPath, "X\t" + bu + "\\" + shi + "\\" + ke + "，人数为" + con);
                        continue;
                    }
                }
                else
                {
                    WriteLog(LogPath, "X\t" + bu + "\\" + shi + "\\" + ke + "，不存在");
                    continue;
                }

                WriteLog(LogPath, bu + "\\" + ke + ".xls");
                importCon++;
                string reportTargetPath = string.Format(@"{0}FengTian\{1}\{2}\", path, language, bu);
                if (string.IsNullOrWhiteSpace(ke)) ke = "随机_ke_" + ToolString.GetRandomStr(10, 6);
                ToolReport.GenerateLocalReport(modelPath, reportTargetPath, ke + ".xls", col, false);
            }
        }

        /// <summary>
        /// 返回部室课的人数
        /// </summary>
        /// <param name="formID"></param>
        /// <param name="isAchieve">是否包含未完成，[False:包含,True:不包含]</param>
        /// <returns></returns>
        public Dictionary<string, int> GetCount(long formID, bool isAchieve = true)
        {
            Dictionary<string, int> dic = new Dictionary<string, int>();
            BaseAsk bf = GetParam(formID);

            string where = "";
            if (isAchieve)
                where = " AND e.[Status] = 100 ";

            string sqlStr = string.Format(@"	
Declare @CompanyID bigint = {0}
Declare @FormApplicationID bigint = {1}
Declare @FormID bigint = {2}

-- 所有部室课数据
	SELECT f.Name ,e.EntryID ,c.Text ,c.ChoiceID
	INTO #oData
	FROM AskForm_Entry e
		INNER JOIN AskForm_EntryChoice ec ON ec.EntryID = e.EntryID
		INNER JOIN AskForm_Field f ON  f.FieldID = ec.FieldID AND f.Name ='Bu'
		INNER JOIN AskForm_Choice c ON c.ChoiceID = ec.[Value] AND c.FieldID = f.FieldID
	WHERE e.CompanyID = @CompanyID
		AND e.FormApplicationID = @FormApplicationID
		AND e.FormID = @FormID
		AND e.IsDeleted = 0
		AND e.[Status] = 100
		AND ec.IsDeleted = 0
		AND f.IsDeleted = 0
		AND c.IsDeleted = 0
UNION ALL
	SELECT f.Name ,e.EntryID ,c.Text ,c.ChoiceID
	FROM AskForm_Entry e
		INNER JOIN AskForm_EntryChoice ec ON ec.EntryID = e.EntryID
		INNER JOIN AskForm_Field f ON  f.FieldID = ec.FieldID AND f.Name ='Shi'
		INNER JOIN AskForm_Choice c ON c.ChoiceID = ec.[Value] AND c.FieldID = f.FieldID
	WHERE e.CompanyID = @CompanyID
		AND e.FormApplicationID = @FormApplicationID
		AND e.FormID = @FormID
		AND e.IsDeleted = 0
		AND e.[Status] = 100
		AND ec.IsDeleted = 0
		AND f.IsDeleted = 0
		AND c.IsDeleted = 0
UNION ALL
	SELECT f.Name ,e.EntryID ,c.Text ,c.ChoiceID
	FROM AskForm_Entry e
		INNER JOIN AskForm_EntryChoice ec ON ec.EntryID = e.EntryID
		INNER JOIN AskForm_Field f ON  f.FieldID = ec.FieldID AND f.Name ='Ke'
		INNER JOIN AskForm_Choice c ON c.ChoiceID = ec.[Value] AND c.FieldID = f.FieldID
	WHERE e.CompanyID = @CompanyID
		AND e.FormApplicationID = @FormApplicationID
		AND e.FormID = @FormID
		AND e.IsDeleted = 0
		AND e.[Status] = 100
		AND ec.IsDeleted = 0
		AND f.IsDeleted = 0
		AND c.IsDeleted = 0

-- 所有部室课领导数据
SELECT e.EntryID ,et.[Value],tp.PositionType,tp.Position
INTO #Leader
FROM AskForm_Entry e
	INNER JOIN AskForm_EntryText et ON et.EntryID = e.EntryID
	INNER JOIN AskForm_Field f ON f.FieldID = et.FieldID AND f.Name = 'EmployeeNo'
	LEFT JOIN Temp_TMCIPosition tp ON CAST(tp.EmployeeNo AS NVARCHAR(MAX)) = et.[Value]
WHERE e.CompanyID = @CompanyID
	AND e.FormApplicationID = @FormApplicationID
	AND e.FormID = @FormID
	AND e.IsDeleted = 0
	AND e.[Status] = 100
	AND  tp.Year = '2018'

-- 删除室领导
DELETE o FROM #oData o 
INNER JOIN #Leader l ON l.EntryID = o.EntryID
WHERE 1=1
	AND o.Name = 'Shi' AND l.PositionType ='室'

-- 删除课领导
DELETE o FROM #oData o 
INNER JOIN #Leader l ON l.EntryID = o.EntryID
WHERE 1=1
	AND o.Name = 'Ke' AND l.PositionType ='课'

SELECT Name,Text,ChoiceID,COUNT(*) AS 'Con' FROM #oData
GROUP BY Name,Text,ChoiceID

DROP TABLE #Leader,#oData", bf.CompanyID, bf.FormApplicationID, bf.FormID, where);

            DataTable dt = DbContent.GetTable(sqlStr);

            if (dt.Rows.Count >= 1)
            {
                string key;
                int val;
                foreach (DataRow dr in dt.Rows)
                {
                    key = dr["Name"] + "_" + dr["ChoiceID"];
                    if (int.TryParse(dr["Con"] + "", out val))
                        dic.Add(key, val);
                }

                return dic;
            }
            return dic;
        }

        #endregion

        #region 修改Excel，并导出

        /// <summary>
        /// 递归修改Excel
        /// </summary>
        /// <param name="path"></param>
        /// <param name="targetPath"></param>
        /// <param name="isToPdf"></param>
        public void ExportPDFByDirectory(string path, string targetPath, bool isToPdf)
        {
            if (!Directory.Exists(targetPath))
                Directory.CreateDirectory(targetPath);

            List<string> excelList = Directory.GetFiles(path).Where(c => c.ToLower().Contains(".xls")).ToList();

            foreach (var item in excelList)
            {
                SetExcel(item, targetPath, isToPdf);
            }

            foreach (var subcatalog in Directory.GetDirectories(path))
            {
                string add = subcatalog.Replace(path, "").Replace("\\", "");
                ExportPDFByDirectory(subcatalog, targetPath + "\\" + add, isToPdf);
            }
        }

        /// <summary>
        /// 设置Excel格式
        /// </summary>
        /// <param name="path"></param>
        /// <param name="targetPath">目标路径</param>
        /// <param name="isToPdf">转PDF</param>
        public void SetExcel(string path, string targetPath, bool isToPdf)
        {
            if (!File.Exists(path))
                return;

            Excel.Application tmpExcel = new Excel.Application();
            object missing = Type.Missing;

            try
            {
                Excel.Workbook tmpbook = tmpExcel.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);


                for (int i = 0; i < tmpbook.Worksheets.Count; i++)
                {
                    Excel.Worksheet objsheet = tmpbook.Worksheets[i + 1];
                    //objsheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;//页面方向横向

                    objsheet.PageSetup.Zoom = false; //打印时页面设置,必须设置为false,下面的二行页高,页宽才有效
                    objsheet.PageSetup.FitToPagesTall = 1; //页高
                    objsheet.PageSetup.FitToPagesWide = 1; //页宽


                    objsheet.PageSetup.CenterHorizontally = true;
                    objsheet.PageSetup.CenterVertically = true;

                    //WriteLog(@"C:\Users\mayn\Desktop\丰田\log.txt", ToolFile.GetFileName(path, false)+"\t"+ objsheet.Name+"\t"+ objsheet.Rows.Count+"\t"+objsheet.StandardHeight);

                }
                string tmpPath = ToolFile.GetAbsolutelyPath(targetPath) + ToolFile.GetFileName(path);

                if (File.Exists(tmpPath))
                    File.Delete(tmpPath);

                tmpbook.Saved = true;
                tmpbook.SaveAs(tmpPath);
                //WriteLog(@"C:\Users\mayn\Desktop\丰田\log.txt","");

                // 导出PDF
                if (isToPdf)
                    ExcelToPdf(tmpPath, Path.Combine(targetPath, ToolFile.GetFileName(path, false) + ".pdf"));
            }
            catch (Exception ex)
            {
                WriteLog(LogPath,ex.Message);
            }
            finally
            {
                tmpExcel.Quit(); PublicMethod.Kill(tmpExcel);
            }
        }

        /// <summary>
        /// Excel转PDF
        /// </summary>
        /// <param name="path"></param>
        /// <param name="targetPath"></param>
        public void ExcelToPdf(string path, string targetPath)
        {
            object missing = Type.Missing;
            Excel.Application tmpExcel = tmpExcel = new Excel.Application();
            Excel.Workbook tmpbook = default(Excel.Workbook);

            if (!File.Exists(path))
                return;
            if (File.Exists(targetPath))
                File.Delete(targetPath);
            try
            {
                tmpbook = tmpExcel.Workbooks.Open(path, missing, missing, missing, missing, missing,
                       missing, missing, missing, missing, missing, missing, missing, missing, missing);
                if (tmpbook != null)
                    tmpbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, targetPath, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
            }
            catch (Exception ex)
            {
                WriteLog(LogPath,ex.Message);
            }
            finally
            {
                if (tmpbook != null) tmpbook.Close();
                if (tmpExcel != null) tmpExcel.Quit();
                PublicMethod.Kill(tmpExcel);
            }
        }

        #endregion

    }
}